﻿Public Class DRUGUSE2CLASS
    Dim connect As CONDB = CONDB.NewConnection()

    Private DUSEID = Nothing
    Private PRDCODE = Nothing
    Private DLABEL = Nothing
    Private DLABEL_EN = Nothing
    Private SCALE = Nothing
    Private T_UNIT = Nothing
    Private QTY = Nothing
    Private U_UNIT = Nothing
    Private USEID = Nothing
    Private DCUID = Nothing
    Private SLID = Nothing
    Private CAUTION = Nothing
    Private CAUTION_EN = Nothing
    Private STATUS = Nothing
    Private USMK = Nothing
    Private USDR = Nothing
    Private DUPDATE = Nothing

    Private selectFrndrguse As String = "SELECT fdu.`DUSEID` AS 'รหัสการใช้ยา', di.`DRGCODE` AS 'รหัสยา', fdu.`PRDCODE` AS 'รหัสผลิตภัณฑ์', fdu.`qty` AS 'จำนวนต่อมื้อ', mp.`PRDNAME` AS 'ชื่อผลิตภัณฑ์', fdu.`dcuid` AS 'รหัสบริโภคยา', mdcu.`code` AS 'code การบริโภคยา', mdcu.`qlabel` AS 'จำนวนการบริโภคไทย', mdcu.`qlabel_en` AS 'จำนวนการบริโภคอังกฤษ', mdcu.`mlabel` AS 'เวลาบริโภคไทย', mdcu.`mlabel_en` AS 'เวลาบริโภคอังกฤษ', fdu.`useid` AS 'รหัสวิธีการใช้ยา', mdu.`dusename` AS 'วิธีการใช้ยา', mdu.`dusename_en` AS 'วิธีการใช้ยาอังกฤษ', fdu.`DLABEL` AS 'ฉลาก', fdu.`DLABEL_EN` AS 'ฉลากภาษาอังกฤษ', fdu.`SCALE` AS 'อัตราส่วน', fdu.`T_UNIT` AS 'รหัสหน่วยนับเวลา', mu1.`UNITNAME_TH` AS 'หน่วยนับเวลา', fdu.`U_UNIT` AS 'รหัสหน่วยนับยา', mu2.`UNITNAME_TH` AS 'หน่วยนับยา', mp.`S_UNIT` AS 'รหัสหน่วยนับขาย', mu3.UNITNAME_TH AS 'หน่วยนับขาย', fdu.`SLID` AS 'รหัสคำเตือน', ml.`SLABELNAME_TH` AS 'ฉลากคำเตือนไทย', ml.`SLABELNAME_EN` AS 'ฉลากคำเตือนอังกฤษ', ml.`T_LABEL` AS 'ชนิดฉลากคำเตือน', fdu.`CAUTION` AS 'คำเตือนเสริมไทย', fdu.`CAUTION_EN` AS 'คำเตือนเสริมอังกฤษ', fdu.`USMK` AS 'รหัสพนักงาน', CONCAT_WS(' ', h1.`NAME`, h1.`LNAME`) AS 'ชื่อพนักงาน', fdu.`USDR` AS 'รหัสผู้ให้บริการ', CONCAT_WS(' ', h2.`NAME`, h2.`LNAME`) AS 'ชื่อผู้ให้บริการ', fdu.`DUPDATE` AS 'วันที่แก้ไขข้อมูลล่าสุด', fdu.`STATUS` AS 'สถานะการเปิดใช้งาน' FROM (SELECT duseid, qty, prdcode, useid, dcuid, dlabel, dlabel_en, scale, t_unit, u_unit, slid, caution, caution_en, usmk, usdr, dupdate, `status` FROM frndrguse) fdu LEFT JOIN (SELECT dcuid, `code`, qlabel, qlabel_en, mlabel, mlabel_en FROM masdrgcuse WHERE `status` = 1) mdcu ON fdu.dcuid = mdcu.dcuid LEFT JOIN (SELECT unitid, unitname_th FROM masunit WHERE `status` = 1) mu1 ON fdu.T_UNIT = mu1.UNITID LEFT JOIN (SELECT unitid, unitname_th FROM masunit WHERE `status` = 1) mu2 ON fdu.U_UNIT = mu2.UNITID LEFT JOIN (SELECT useid, ugid, dusename, dusename_en FROM masdrguse WHERE `status` = 1) mdu ON fdu.useid = mdu.useid LEFT JOIN (SELECT prdcode, prdname, s_unit FROM masproduct WHERE `status` = 1) mp ON fdu.PRDCODE = mp.PRDCODE LEFT JOIN (SELECT unitid, unitname_th FROM masunit WHERE `status` = 1) mu3 ON mp.S_UNIT = mu3.UNITID LEFT JOIN (SELECT slid, slabelname_th, slabelname_en, t_label FROM masslabel WHERE `status` = 1) ml ON fdu.SLID = ml.SLID LEFT JOIN (SELECT empid, `name`, lname FROM hospemp) h1 ON fdu.USMK = h1.EMPID LEFT JOIN (SELECT empid, `name`, lname FROM hospemp) h2 ON fdu.USDR = h2.EMPID LEFT JOIN (SELECT drgcode, prdcode FROM drugitem WHERE `status` = 1) di ON mp.PRDCODE = di.PRDCODE "

    Public Property DUSEID_ As Integer
        Get
            Return DUSEID
        End Get
        Set(value As Integer)
            DUSEID = value
        End Set
    End Property

    Public Property PRDCODE_ As Integer
        Get
            Return PRDCODE
        End Get
        Set(value As Integer)
            PRDCODE = value
        End Set
    End Property

    Public Property DLABEL_ As String
        Get
            Return DLABEL
        End Get
        Set(value As String)
            DLABEL = value
        End Set
    End Property

    Public Property DLABEL_EN_ As String
        Get
            Return DLABEL_EN
        End Get
        Set(value As String)
            DLABEL_EN = value
        End Set
    End Property

    Public Property SCALE_ As Double
        Get
            Return SCALE
        End Get
        Set(value As Double)
            SCALE = value
        End Set
    End Property

    Public Property T_UNIT_ As Integer
        Get
            Return T_UNIT
        End Get
        Set(value As Integer)
            T_UNIT = value
        End Set
    End Property

    Public Property QTY_ As String
        Get
            Return QTY
        End Get
        Set(value As String)
            QTY = value
        End Set
    End Property

    Public Property U_UNIT_ As Integer
        Get
            Return U_UNIT
        End Get
        Set(value As Integer)
            U_UNIT = value
        End Set
    End Property

    Public Property USEID_ As Integer
        Get
            Return USEID
        End Get
        Set(value As Integer)
            USEID = value
        End Set
    End Property

    Public Property DCUID_ As Integer
        Get
            Return DCUID
        End Get
        Set(value As Integer)
            DCUID = value
        End Set
    End Property

    Public Property SLID_ As Integer
        Get
            Return SLID
        End Get
        Set(value As Integer)
            SLID = value
        End Set
    End Property

    Public Property CAUTION_ As String
        Get
            Return CAUTION
        End Get
        Set(value As String)
            CAUTION = value
        End Set
    End Property

    Public Property CAUTION_EN_ As String
        Get
            Return CAUTION_EN
        End Get
        Set(value As String)
            CAUTION_EN = value
        End Set
    End Property

    Property STATUS_ As Boolean
        Get
            Return STATUS
        End Get
        Set(value As Boolean)
            If value = True Then
                STATUS = 1
            Else
                STATUS = 0
            End If
        End Set
    End Property

    Public Property USMK_ As Integer
        Get
            Return USMK
        End Get
        Set(value As Integer)
            USMK = value
        End Set
    End Property

    Public Property USDR_ As Integer
        Get
            Return USDR
        End Get
        Set(value As Integer)
            USDR = value
        End Set
    End Property

    Public Property selectFrndrguse_ As String
        Get
            Return selectFrndrguse
        End Get
        Set(value As String)
            selectFrndrguse = value
        End Set
    End Property

    Public Function selectFilterFrndrguse(prdcode As Integer) As DataTable
        Dim dt As DataTable = connect.GetTable("SELECT fdu.`DUSEID` AS 'รหัสการใช้ยา', di.`DRGCODE` AS 'รหัสยา', fdu.`PRDCODE` AS 'รหัสผลิตภัณฑ์', fdu.`qty` AS 'จำนวนต่อมื้อ', mp.`PRDNAME` AS 'ชื่อผลิตภัณฑ์', fdu.`dcuid` AS 'รหัสบริโภคยา', mdcu.`code` AS 'code การบริโภคยา', mdcu.`qlabel` AS 'จำนวนการบริโภคไทย', mdcu.`qlabel_en` AS 'จำนวนการบริโภคอังกฤษ', mdcu.`mlabel` AS 'เวลาบริโภคไทย', mdcu.`mlabel_en` AS 'เวลาบริโภคอังกฤษ', fdu.`useid` AS 'รหัสวิธีการใช้ยา', mdu.`dusename` AS 'วิธีการใช้ยา', mdu.`dusename_en` AS 'วิธีการใช้ยาอังกฤษ', fdu.`DLABEL` AS 'ฉลาก', fdu.`DLABEL_EN` AS 'ฉลากภาษาอังกฤษ', fdu.`SCALE` AS 'อัตราส่วน', fdu.`T_UNIT` AS 'รหัสหน่วยนับเวลา', mu1.`UNITNAME_TH` AS 'หน่วยนับเวลา', fdu.`U_UNIT` AS 'รหัสหน่วยนับยา', mu2.`UNITNAME_TH` AS 'หน่วยนับยา', mp.`S_UNIT` AS 'รหัสหน่วยนับขาย', mu3.UNITNAME_TH AS 'หน่วยนับขาย', fdu.`SLID` AS 'รหัสคำเตือน', ml.`SLABELNAME_TH` AS 'ฉลากคำเตือนไทย', ml.`SLABELNAME_EN` AS 'ฉลากคำเตือนอังกฤษ', ml.`T_LABEL` AS 'ชนิดฉลากคำเตือน', fdu.`CAUTION` AS 'คำเตือนเสริมไทย', fdu.`CAUTION_EN` AS 'คำเตือนเสริมอังกฤษ', fdu.`USMK` AS 'รหัสพนักงาน', CONCAT_WS(' ', h1.`NAME`, h1.`LNAME`) AS 'ชื่อพนักงาน', fdu.`USDR` AS 'รหัสผู้ให้บริการ', CONCAT_WS(' ', h2.`NAME`, h2.`LNAME`) AS 'ชื่อผู้ให้บริการ', fdu.`DUPDATE` AS 'วันที่แก้ไขข้อมูลล่าสุด', fdu.`STATUS` AS 'สถานะการเปิดใช้งาน' FROM (SELECT duseid, qty, prdcode, useid, dcuid, dlabel, dlabel_en, scale, t_unit, u_unit, slid, caution, caution_en, usmk, usdr, dupdate, `status` FROM frndrguse) fdu LEFT JOIN (SELECT dcuid, `code`, qlabel, qlabel_en, mlabel, mlabel_en FROM masdrgcuse WHERE `status` = 1) mdcu ON fdu.dcuid = mdcu.dcuid LEFT JOIN (SELECT unitid, unitname_th FROM masunit WHERE `status` = 1) mu1 ON fdu.T_UNIT = mu1.UNITID LEFT JOIN (SELECT unitid, unitname_th FROM masunit WHERE `status` = 1) mu2 ON fdu.U_UNIT = mu2.UNITID LEFT JOIN (SELECT useid, ugid, dusename, dusename_en FROM masdrguse WHERE `status` = 1) mdu ON fdu.useid = mdu.useid LEFT JOIN (SELECT prdcode, prdname, s_unit FROM masproduct WHERE `status` = 1) mp ON fdu.PRDCODE = mp.PRDCODE LEFT JOIN (SELECT unitid, unitname_th FROM masunit WHERE `status` = 1) mu3 ON mp.S_UNIT = mu3.UNITID LEFT JOIN (SELECT slid, slabelname_th, slabelname_en, t_label FROM masslabel WHERE `status` = 1) ml ON fdu.SLID = ml.SLID LEFT JOIN (SELECT empid, `name`, lname FROM hospemp) h1 ON fdu.USMK = h1.EMPID LEFT JOIN (SELECT empid, `name`, lname FROM hospemp) h2 ON fdu.USDR = h2.EMPID LEFT JOIN (SELECT drgcode, prdcode FROM drugitem WHERE `status` = 1) di ON mp.PRDCODE = di.PRDCODE WHERE fdu.PRDCODE = '" & prdcode & "'")
        connect.Dispose()
        Return dt
    End Function

    Public Function selectDrgcuse(dcuid As String) As DataTable
        Dim dt As DataTable = connect.GetTable("SELECT dcuid, qlabel, qlabel_en, mlabel, mlabel_en FROM masdrgcuse WHERE dcuid = '" & dcuid & "'")
        connect.Dispose()
        Return dt
    End Function

    Public Function selectSlabel(slid As String) As DataTable
        Dim dt As DataTable = connect.GetTable("SELECT slabelname_th, slabelname_en FROM masslabel WHERE slid = '" & slid & "'")
        connect.Dispose()
        Return dt
    End Function

    Public Function selectMasdrguse(useid As String) As DataTable
        Dim dt As DataTable = connect.GetTable("SELECT ugid, dusename, dusename_en, ucode FROM masdrguse WHERE useid = '" & useid & "'")
        connect.Dispose()
        Return dt
    End Function

    '---------------------------------------------------------กลุ่มตัวแปร และฟังก์ชั่น สำหรับใช้สร้างวิธีการใช้ยาแบบอัตโนมัติ---------------------------------------------------------

    Public Shared Function HALFString() As String
        Return "ครึ่ง"
    End Function

    Public Shared Function modnumber(num1 As Integer, num2 As Integer) As Integer
        Return num1 Mod num2
    End Function

    Public Shared Function divideNumber(num1 As Integer, num2 As Integer) As Integer
        Return num1 \ num2
    End Function

    Public Shared Function ConvertStringTonum(str As String) As String
        Select Case str
            Case "สิบ"
                Return "10"
            Case "เก้า"
                Return "9"
            Case "แปด"
                Return "8"
            Case "เจ็ด"
                Return "7"
            Case "หก"
                Return "6"
            Case "ห้า"
                Return "5"
            Case "สี่"
                Return "4"
            Case "สาม"
                Return "3"
            Case "สอง"
                Return "2"
            Case "หนึ่ง"
                Return "1"
            Case Else
                Return ""
        End Select

    End Function

    Public Shared Function convertNumToStringThai(num As Integer) As String
        Select Case num
            Case 10
                Return "สิบ"
            Case "9"
                Return "เก้า"
            Case "8"
                Return "แปด"
            Case "7"
                Return "เจ็ด"
            Case "6"
                Return "หก"
            Case "5"
                Return "ห้า"
            Case "4"
                Return "สี่"
            Case "3"
                Return "สาม"
            Case "2"
                Return "สอง"
            Case "1"
                Return "หนึ่ง"
            Case Else
                Return ""
        End Select
    End Function

    '-----------------------------------------------------end of กลุ่มตัวแปร และฟังก์ชั่น สำหรับใช้สร้างวิธีการใช้ยาแบบอัตโนมัติ------------------------------------------------------

    Public Function insertFrndrguse() As Boolean
        If connect.GetTable("SELECT duseid FROM frndrguse WHERE prdcode = '" & PRDCODE & "' AND dlabel = '" & DLABEL & "' AND u_unit = '" & U_UNIT & "' AND caution = '" & CAUTION & "' AND caution_en = '" & CAUTION_EN & "'").Rows.Count > 0 Then
            MsgBox("ไม่สามารถเพิ่มการใช้ยาได้ เนื่องจากมีรูปแบบการใช้ยานี้อยู่ในระบบแล้ว")
            Return False
        Else
            Dim sql1 As String = ""
            Dim sql2 As String = ""
            Dim sql3 As String = ""

            If PRDCODE IsNot Nothing Then
                sql2 += "prdcode, "
                sql3 += "'" & PRDCODE & "', "
            Else
                sql2 += "prdcode, "
                sql3 += "default, "
            End If

            If DLABEL IsNot Nothing Then
                sql2 += "dlabel, "
                sql3 += "'" & DLABEL & "', "
            Else
                sql2 += "dlabel, "
                sql3 += "default, "
            End If

            If DLABEL_EN IsNot Nothing Then
                sql2 += "dlabel_en, "
                sql3 += "'" & DLABEL_EN & "', "
            Else
                sql2 += "dlabel_en, "
                sql3 += "default, "
            End If

            If SCALE IsNot Nothing Then
                sql2 += "scale, "
                sql3 += "'" & SCALE & "', "
            Else
                sql2 += "scale, "
                sql3 += "default, "
            End If

            If T_UNIT IsNot Nothing Then
                sql2 += "t_unit, "
                sql3 += "'" & T_UNIT & "', "
            Else
                sql2 += "t_unit, "
                sql3 += "default, "
            End If

            If QTY IsNot Nothing Then
                sql2 += "qty, "
                sql3 += "'" & QTY & "', "
            Else
                sql2 += "qty, "
                sql3 += "default, "
            End If

            If U_UNIT IsNot Nothing Then
                sql2 += "u_unit, "
                sql3 += "'" & U_UNIT & "', "
            Else
                sql2 += "u_unit, "
                sql3 += "default, "
            End If

            If USEID IsNot Nothing Then
                sql2 += "useid, "
                sql3 += "'" & USEID & "', "
            Else
                sql2 += "useid, "
                sql3 += "default, "
            End If

            If DCUID IsNot Nothing Then
                sql2 += "dcuid, "
                sql3 += "'" & DCUID & "', "
            Else
                sql2 += "dcuid, "
                sql3 += "default, "
            End If

            If SLID IsNot Nothing Then
                sql2 += "slid, "
                sql3 += "'" & SLID & "', "
            Else
                sql2 += "slid, "
                sql3 += "default, "
            End If

            If CAUTION IsNot Nothing Then
                sql2 += "caution, "
                sql3 += "'" & CAUTION & "', "
            Else
                sql2 += "caution, "
                sql3 += "default, "
            End If

            If CAUTION_EN IsNot Nothing Then
                sql2 += "caution_en, "
                sql3 += "'" & CAUTION_EN & "', "
            Else
                sql2 += "caution_en, "
                sql3 += "default, "
            End If

            If STATUS IsNot Nothing Then
                sql2 += "status, "
                sql3 += "'" & STATUS & "', "
            Else
                sql2 += "status, "
                sql3 += "default, "
            End If

            If USMK IsNot Nothing Then
                sql2 += "usmk, "
                sql3 += "'" & USMK & "', "
            Else
                sql2 += "usmk, "
                sql3 += "default, "
            End If

            sql2 += "dupdate"
            sql3 += "NOW()"

            sql1 += "INSERT INTO frndrguse (" & sql2 & ") VALUES (" & sql3 & ");"

            Try
                connect.BeginTrans()
                connect.ExecuteNonQuery(sql1)
                connect.CommitTrans()
                Return True
            Catch ex As Exception
                MsgBox(ex.ToString)
                Return False
            Finally
                connect.Dispose()
            End Try
        End If
    End Function

    Public Function editFrndrguse() As Boolean
        Dim sql As String = ""
        sql += "UPDATE frndrguse SET "
     
        sql += "prdcode = '" & PRDCODE & "', "
        sql += "dlabel = '" & DLABEL & "', "
        sql += "dlabel_en = '" & DLABEL_EN & "', "

        If SCALE IsNot Nothing Then
            sql += "scale = '" & SCALE & "', "
        End If

        If T_UNIT IsNot Nothing Then
            sql += "t_unit = '" & T_UNIT & "', "
        End If

        If QTY IsNot Nothing Then
            sql += "qty = '" & QTY & "', "
        End If

        If U_UNIT IsNot Nothing Then
            sql += "u_unit = '" & U_UNIT & "', "
        End If

        If USEID IsNot Nothing Then
            sql += "useid = '" & USEID & "', "
        End If

        If DCUID IsNot Nothing Then
            sql += "dcuid = '" & DCUID & "', "
        End If

        If SLID IsNot Nothing Then
            sql += "slid = '" & SLID & "', "
        End If

        If CAUTION IsNot Nothing Then
            sql += "caution = '" & CAUTION & "', "
        End If

        If CAUTION_EN IsNot Nothing Then
            sql += "caution_en = '" & CAUTION_EN & "', "
        End If

        sql += "status = '" & STATUS & "', "

        If USMK Is Nothing Then
            sql += "usmk = null, "
        Else
            sql += "usmk = '" & USMK & "', "
        End If

        sql += "dupdate = NOW() "

        sql += "WHERE duseid = '" & DUSEID & "'"
        'Dim sql As String = "UPDATE frndrguse SET prdcode = '" & PRDCODE & "', dlabel = '" & DLABEL & "', dlabel_en = '" & DLABEL_EN & "', qty = '" & QTY & "',u_unit = '" & U_UNIT & "', useid = '" & USEID & "', dcuid = '" & DCUID & "'"

        'If SLID = Nothing Then
        '    sql += "slid = null, "
        '    sql += "caution = '" & CAUTION & "', "
        '    sql += "caution_en = '" & CAUTION_EN & "', "
        'Else
        '    sql += "slid = '" & SLID & "', "
        '    sql += "caution = null, "
        '    sql += "caution_en = null, "
        'End If

        'sql += "status = '" & STATUS & "', usmk = '" & USMK & "', usdr = '" & USDR & "', dupdate = NOW() WHERE duseid = '" & DUSEID & "'"

        Try
            connect.BeginTrans()
            connect.ExecuteNonQuery(sql)
            connect.CommitTrans()
            Return True
        Catch ex As Exception
            MsgBox(ex.ToString)
            Return False
        Finally
            connect.Dispose()
        End Try
    End Function
End Class
